﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.Store;
using System.Data;
using System.Data.SqlClient;

namespace YXShop.SQLServerDAL.Store
{
    public class Store : IStore
    {
        #region DataBase Operation

        public int Add(YXShop.Model.Store.Store model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into yxs_store(");
            strSql.Append("adminid,storecenterid,parentstoreid,storename,storetype,discription,startdate,enddate,state,addtime,updatetime,province,city,borough,address,zipcode,mobilephone,phone,contact,fax)");
            strSql.Append(" values(");
            strSql.Append("@adminid,@storecenterid,@parentstoreid,@storename,@storetype,@discription,@startdate,@enddate,@state,@addtime,@updatetime,@province,@city,@borough,@address,@zipcode,@mobilephone,@phone,@contact,@fax)");
            strSql.Append(" Select scope_IDENTITY()");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParas(model);
            object obj = ChangeHope.DataBase.SQLServerHelper.GetSingle(strSql.ToString(),parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        public int Update(YXShop.Model.Store.Store model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [yxs_store] set ");
            strSql.Append("adminid=@adminid,storecenterid=@storecenterid,parentstoreid=@parentstoreid,storename=@storename,storetype=@storetype,discription=@discription,startdate=@startdate,enddate=@enddate,state=@state,addtime=@addtime,updatetime=@updatetime,province=@province,city=@city,borough=@borough,address=@address,zipcode=@zipcode,mobilephone=@mobilephone,phone=@phone,contact=@contact,fax=@fax");
            strSql.Append(" where sid=@sid ");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParas(model);
            return ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        public void Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete  from [yxs_store] ");
            strSql.Append(" where sid=@sid");
            SqlParameter[] parameter = { new SqlParameter("@sid",SqlDbType.Int,4)};
            parameter[0].Value = id;
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameter);

        }


        public void DeleteAll(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_store] where sid in(");
            strSql.Append(" "+ids+" )");
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        }
      
        #endregion

        #region DataBase Load

        public YXShop.Model.Store.Store GetModelById(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from yxs_store ");
            strSql.Append(" where sid=@sid ");
            SqlParameter[] parameter ={
                                          new SqlParameter("@sid",SqlDbType.Int,4)
                                     };
            parameter[0].Value = id;
            YXShop.Model.Store.Store model = null;
            using(SqlDataReader reader=ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString(),parameter))
            {
                while(reader.Read())
                {   
                    model=new YXShop.Model.Store.Store();
                    model.Sid = Convert.ToInt32(reader["sid"]);
                    model.Adminid = Convert.ToInt32(reader["adminid"]);
                    model.Storecenterid = Convert.ToInt32(reader["storecenterid"]);
                    model.Parentstoreid = Convert.ToInt32(reader["parentstoreid"]);
                    model.Storename = (string)reader["storename"];
                    model.Storetype = Convert.ToInt32(reader["storetype"]);
                    model.Discription = (string)reader["discription"];
                    model.Startdate = Convert.ToDateTime(reader["startdate"]);
                    model.Enddate = Convert.ToDateTime(reader["enddate"]);
                    model.State = Convert.ToInt32(reader["state"]);
                    model.Addtime = Convert.ToDateTime(reader["addtime"]);
                    model.Updatetime = Convert.ToDateTime(reader["updatetime"]);
                    model.Province = (string)reader["province"];
                    model.City = (string)reader["city"];
                    model.Borough = (string)reader["borough"];
                    model.Address = (string)reader["address"];
                    model.Zipcode = (string)reader["zipcode"];
                    model.Mobilephone = (string)reader["mobilephone"];
                    model.Phone = (string)reader["phone"];
                    model.Contact = (string)reader["contact"];
                    model.Fax = (string)reader["fax"];
                }
            }
            return model;
        }

        public List<YXShop.Model.Store.Store> GetListStoreByWhere(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from yxs_store ");
            if(where!=null&&where!="")
            {
                strSql.Append(" where 1=1 "+where+" ");
            }
            List<YXShop.Model.Store.Store> storeList = new List<YXShop.Model.Store.Store>();
            using (SqlDataReader reader = ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                while (reader.Read())
                {
                    YXShop.Model.Store.Store model = new YXShop.Model.Store.Store();
                    model.Sid = Convert.ToInt32(reader["sid"]);
                    model.Adminid = Convert.ToInt32(reader["adminid"]);
                    model.Storecenterid = Convert.ToInt32(reader["storecenterid"]);
                    model.Parentstoreid = Convert.ToInt32(reader["parentstoreid"]);
                    model.Storename = (string)reader["storename"];
                    model.Storetype = Convert.ToInt32(reader["storetype"]);
                    model.Discription = (string)reader["discription"];
                    model.Startdate = Convert.ToDateTime(reader["startdate"]);
                    model.Enddate = Convert.ToDateTime(reader["enddate"]);
                    model.State = Convert.ToInt32(reader["state"]);
                    model.Addtime = Convert.ToDateTime(reader["addtime"]);
                    model.Updatetime = Convert.ToDateTime(reader["updatetime"]);
                    model.Province = (string)reader["province"];
                    model.City = (string)reader["city"];
                    model.Borough = (string)reader["borough"];
                    model.Address = (string)reader["address"];
                    model.Zipcode = (string)reader["zipcode"];
                    model.Mobilephone = (string)reader["mobilephone"];
                    model.Phone = (string)reader["phone"];
                    model.Contact = (string)reader["contact"];
                    model.Fax = (string)reader["fax"];
                    storeList.Add(model);
                }
            }
            return storeList;
        }
        public ChangeHope.DataBase.DataByPage GetList(string strWhere)
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("[select] * [from] yxs_store [where] 1=1 ");
            if(strWhere!=null&&strWhere!="")
            {
                strSql.Append(""+strWhere);
            }
            strSql.Append(" [order by] sid desc");
            dataPage.Sql = strSql.ToString();
            dataPage.GetRecordSetByPage();
            return dataPage;
        }

        /// <summary>
        /// 排序所有记录
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetList(string orderfield, int pagesize, string Conditions)
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = "[select] * [from] yxs_store [where] 1=1 " + Conditions + " " + orderfield;
            dataPage.PageSize = pagesize;
            dataPage.GetRecordSetByPage();
            return dataPage;
        }

        #endregion

        #region Other Function
        public IDbDataParameter[] ValueParas(YXShop.Model.Store.Store model)
        {

            SqlParameter[] parameters ={
                                          new SqlParameter("@sid",SqlDbType.Int,4),
                                          new SqlParameter("@adminid",SqlDbType.Int,4),
                                          new SqlParameter("@storecenterid",SqlDbType.Int,4),
                                          new SqlParameter("@parentstoreid",SqlDbType.Int,4),
                                          new SqlParameter("@storename",SqlDbType.NVarChar,50),
                                          new SqlParameter("@storetype",SqlDbType.Int,4),
                                         
                                          new SqlParameter("@discription",SqlDbType.NText),
                                          new SqlParameter("@startdate",SqlDbType.DateTime,8),
                                          new SqlParameter("@enddate",SqlDbType.DateTime,8),

                                          new SqlParameter("@state",SqlDbType.Int,4),
                                          new SqlParameter("@addtime",SqlDbType.DateTime,8),

                                          new SqlParameter("@updatetime",SqlDbType.DateTime,8),
                                          new SqlParameter("@province",SqlDbType.VarChar,20),
                                          new SqlParameter("@city",SqlDbType.VarChar,20),
                                          new SqlParameter("@borough",SqlDbType.VarChar,20),
                                          new SqlParameter("@address",SqlDbType.NVarChar,50),
                                          new SqlParameter("@zipcode",SqlDbType.NVarChar,50),
                                          new SqlParameter("@mobilephone",SqlDbType.NVarChar,50),
                                          new SqlParameter("@phone",SqlDbType.NVarChar,50),
                                          new SqlParameter("@contact",SqlDbType.NVarChar,50),
                                          new SqlParameter("@fax",SqlDbType.NVarChar,50),

                                      };
            parameters[0].Value = model.Sid;
            parameters[1].Value = model.Adminid;
            parameters[2].Value = model.Storecenterid;
            parameters[3].Value = model.Parentstoreid;
            parameters[4].Value = model.Storename;
            parameters[5].Value = model.Storetype;

            parameters[6].Value = model.Discription;
            parameters[7].Value = model.Startdate;
            parameters[8].Value = model.Enddate;
            parameters[9].Value = model.State;
            parameters[10].Value = model.Addtime;

            parameters[11].Value = model.Updatetime;
            parameters[12].Value = model.Province;
            parameters[13].Value = model.City;
            parameters[14].Value = model.Borough;
            parameters[15].Value = model.Address;

            parameters[16].Value = model.Zipcode;
            parameters[17].Value = model.Mobilephone;
            parameters[18].Value = model.Phone;

            parameters[19].Value = model.Contact;
            parameters[20].Value = model.Fax;

            return parameters;
        }
        #endregion

       
    }
}
